#基于pymysql
import pymysql
from secure import DHOST,DPORT,DUSER,DPWD,DATABASE,CHARSET

class DB:
    # 构造函数
    def __init__(self,data):
        try:
            # 获取数据库连接
            self.conn = pymysql.connect(**data)
            # 获取游标
            self.cursor = self.conn.cursor(pymysql.cursors.DictCursor)
        except Exception as e:
            print('*'*10)
            print(e)
            print('*'*10)
    # 增
    def insert(self,sql):
        try:
            self.cursor.execute(sql)
            self.conn.commit()
            return self.cursor.lastrowid
        except Exception as e:
            print(e)
            self.conn.rollback()
            return 0
    #删
    def delete(self,sql,data):
        try:
            self.cursor.execute(sql,data)
            self.conn.commit()
            return self.conn.affected_rows()
        except:
            self.conn.rollback()
            return 0
    #改
    def update(self,sql,data):
        try:
            self.cursor.execute(sql,data)
            self.conn.commit()
            return self.conn.affected_rows()
        except Exception as e:
            print(e)
            self.conn.rollback()
            return 0
    #查
    def select(self,sql,data=[]):
        try:
            if not data:
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql,data)
            data = self.cursor.fetchall()
            return data
        except:
            return []

    # 查单条数据
    def get_one(self,sql,data):
        try:
            self.cursor.execute(sql,data)
            data = self.cursor.fetchone()
            return data
        except:
            return {}
    #析构函数
    def __del__(self):
        if self.cursor:
            self.cursor.close()
        if self.conn:
            self.conn.close()


config = {
    "host":DHOST,
    "port":DPORT,
    "user":DUSER,
    "password":DPWD,
    "database":DATABASE,
    'charset':CHARSET
}

#查看
# sql = 'select * from user'
# print(db.select(sql))

#添加
# sql = 'insert into user (name)values ("小花1")'
# print(db.insert(sql))

#删除
# sql = "delete from user where name='小花1';"
# print(db.delete(sql))

#修改
# sql = "update user set name='{}' where id ={}".format('赵鹤',2)
# print(db.update(sql))
